﻿Imports System.Data.SqlClient

Public Class frmProspectsReassign

    Sub FillProspects()
        Try
            Me.Cursor = Cursors.WaitCursor

            'Check if Assigned/Unassigned
            Dim Cond As String = ""

            'Check Expiry Date
            If Me.RExpireIn75Days.Checked = True Then
                Cond += " and DateDiff(d,GetDate(),ExpiryDate)<=75"
            End If

            'Check Filter
            If Me.RFilterCompanyName.Checked = True Then
                Cond += " and InsuranceCompany Like N'%" & Me.txtCompanyName.Text.Trim & "%'"
            End If

            Dim cmd As New SqlCommand("Select SNo,PNo,ProsNo,ProsType,Status,ProspectName,InsuranceCompany,InsuranceBroker,ClassofBusiness,BusinessSize" & _
                                      ",ExpiryDate,IsNull(AssignedTo,N'') AssignedTo," & _
                                      "IsNull(AssignedBy,N'') AssignedBy,AssignedDate,SavedUser," & _
                                      "TransDate,IsNull(dbo.GetProspectLastUpdate(SNo),N'') LastUpdate,SentToUR From " & _
                                      "ProspectCases Where Branch=N'" & Branch & "' and Status=N'Active' " & Cond, cnn)

            Dim Reader As SqlDataReader
            Dim AssignDate, ExpiryDate As String

            Me.GridProspects.Rows.Clear()
            Me.txtCompanyName.AutoCompleteCustomSource.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("AssignedDate").ToString = "1/1/1900 12:00:00 AM" Then
                    AssignDate = ""
                Else
                    AssignDate = CDate(Reader.Item("AssignedDate")).ToString("yyyy/MM/dd")
                End If

                If Reader.Item("ExpiryDate") Is DBNull.Value Then
                    ExpiryDate = ""
                Else
                    ExpiryDate = CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd")
                End If

                Me.GridProspects.Rows.Add(New String() {Reader.Item("SNo"), "N", Reader.Item("ProsNo"), Reader.Item("Status"),ExpiryDate, _
                                                        Reader.Item("InsuranceCompany"), Reader.Item("InsuranceBroker"), _
                                                        Reader.Item("ClassofBusiness"), Reader.Item("BusinessSize"), _
                                                        Reader.Item("AssignedTo"), Reader.Item("AssignedBy"), AssignDate, _
                                                        Reader.Item("SavedUser"), Format(CDate(Reader.Item("TransDate")), "yyyy/MM/dd"), _
                                                        CDate(Reader.Item("LastUpdate")).ToString("yyyy/MM/dd")})
                Me.txtCompanyName.AutoCompleteCustomSource.Add(Reader.Item("InsuranceCompany"))
            End While
            cnn.Close()

            'Check Colors
            For Each row As DataGridViewRow In Me.GridProspects.Rows
                If row.Cells(3).Value = "Active" Then
                    row.Cells(3).Style.BackColor = Color.Yellow

                ElseIf row.Cells(3).Value = "Not Active" Then
                    row.Cells(3).Style.BackColor = Color.Magenta

                End If
            Next

            'Check Expiry Date
            For Each Row As DataGridViewRow In Me.GridProspects.Rows
                If Now.Date >= CType(Row.Cells(4).Value, Date).Date Then
                    Row.Cells(4).Style.BackColor = Color.Red
                Else
                    Row.Cells(4).Style.BackColor = Color.White
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            'MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillUsersList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select FullName,Email From Users Order By FullName", cnn)
            Dim Reader As SqlDataReader

            Me.ListSalesAgents.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.ListSalesAgents.Items.Add(Reader.Item("FullName"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmProspectsAssign_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
        FillProspects()
        FillUsersList()
    End Sub

    Private Sub RAssigned_CheckedChanged(sender As System.Object, e As System.EventArgs)
        FillProspects()
    End Sub

    Private Sub RUnAssigned_CheckedChanged(sender As System.Object, e As System.EventArgs)
        FillProspects()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If Me.ListSalesAgents.SelectedItems.Count = 0 Then
            MsgBox("Please select a sales agent from the list")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction
                Dim X As Integer = 0
                Dim CurrentIndex As Integer

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                For Each row As DataGridViewRow In Me.GridProspects.Rows
                    If row.Cells(1).Value = "Y" Then
                        cmd.CommandText = "Update ProspectCases Set Status=@Status,AssignedTo=@AssignedTo,AssignedBy=@AssignedBy,AssignedDate=GetDate() Where SNo=" & CInt(row.Cells(0).Value)

                        'Add Values
                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@Status", "Active")
                        cmd.Parameters.AddWithValue("@AssignedTo", Me.ListSalesAgents.SelectedItem)
                        cmd.Parameters.AddWithValue("@AssignedBy", CurrentUser)
                        cmd.ExecuteNonQuery()

                        'Add Note to show re-assinging details
                        cmd.CommandText = "Insert Into ProspectsNotes (ProsNo,Notes,SavedUser) Values " & _
                                          "(@ProsNo,@Notes,@SavedUser)"

                        'Add Values
                        cmd.Parameters.Clear()
                        cmd.Parameters.AddWithValue("@ProsNo", CInt(row.Cells(0).Value))
                        cmd.Parameters.AddWithValue("@Notes", "Prospect reassigned to: " & Me.ListSalesAgents.SelectedItem)
                        cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                        cmd.ExecuteNonQuery()

                        X += 1
                        CurrentIndex = row.Index
                    End If
                Next

                Trans.Commit()
                cnn.Close()

                'Sent notification if prospects have been assigned
                If X > 0 Then
                    Dim Subject, Body As String
                    Dim Receivers As New ArrayList

                    Subject = "Oasis - IBS Notification - Sales Management System"
                    Body = "Your received a notification from: <B>" & CurrentUser & "</B>. " & _
                           "<B>New  prospects have been assigned to you.</B><br/><br/>" & _
                           "Please don't reply to this email.<br/>" & _
                           "Sent from Oasis - Insurance Broker System"
                    Receivers.Add(GetUserEmail(Me.ListSalesAgents.SelectedItem))

                    SendEmail(Subject, Body, Receivers)

                    MsgBox("Notification has been sent successfully")
                End If

                FillProspects()

                If Me.GridProspects.Rows.Count > 0 Then
                    Try
                        Me.GridProspects.CurrentCell = Me.GridProspects.Rows(CurrentIndex).Cells(5)
                    Catch ex As Exception
                        Me.GridProspects.CurrentCell = Me.GridProspects.Rows(Me.GridProspects.Rows.Count - 1).Cells(5)
                    End Try
                End If

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub RExpireIn75Days_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RExpireIn75Days.CheckedChanged
        FillProspects()
    End Sub

    Private Sub RShowAll_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RShowAll.CheckedChanged
        FillProspects()
    End Sub

    Sub CheckStatus()
        If Me.RFilterCompanyName.Checked = True Then
            Me.txtCompanyName.Enabled = True
            Me.txtCompanyName.Focus()

        ElseIf Me.RFilterAll.Checked = True Then
            Me.txtCompanyName.Enabled = False
        End If
    End Sub

    Private Sub RFilterCompanyName_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RFilterCompanyName.CheckedChanged
        CheckStatus()
    End Sub

    Private Sub RFilterAll_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RFilterAll.CheckedChanged
        CheckStatus()
        FillProspects()
    End Sub

    Private Sub txtCompanyName_KeyUp(sender As System.Object, e As System.Windows.Forms.KeyEventArgs) Handles txtCompanyName.KeyUp
        If e.KeyCode = Keys.Enter Then
            FillProspects()
        End If
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        FillProspects()
    End Sub

    Private Sub GridProspects_CellDoubleClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProspects.CellDoubleClick
        If e.RowIndex <> -1 Then
            Dim a As New frmProspectsDetails
            Dim CurrentIndex As Integer

            a.SNo = CInt(Me.GridProspects.Rows(e.RowIndex).Cells(0).Value)
            CurrentIndex = e.RowIndex

            a.ShowDialog()

            FillProspects()

            If Me.GridProspects.Rows.Count > 0 Then
                Try
                    Me.GridProspects.CurrentCell = Me.GridProspects.Rows(CurrentIndex).Cells(5)
                Catch ex As Exception
                    Me.GridProspects.CurrentCell = Me.GridProspects.Rows(Me.GridProspects.Rows.Count - 1).Cells(5)
                End Try
            End If
        End If
    End Sub
End Class